import pandas as pd
import os


file_path = 'E:/M/MathModel/23国赛C题/ds/附件5.xlsx'
sale = pd.read_excel(file_path, sheet_name='合并')
# 销售日期 扫码销售时间	单品编码	销量(千克)	销售单价(元/千克)	销售类型	是否打折销售	单品名称	分类编码	分类名称	批发价

# 按日期统计退货订单数
return_orders = sale[sale['销售类型'] == '退货']
return_orders['销售日期'] = pd.to_datetime(return_orders['销售日期'])
return_by_date = return_orders.groupby(return_orders['销售日期'].dt.date)['销售类型'].count().reset_index()
return_by_date.columns = ['销售日期', '退货订单数']
print("\n按日期统计的退货订单数:")
print(return_by_date)

# 统计每个产品出现的次数
product_code_counts = sale['单品编码'].value_counts().reset_index()
product_code_counts.columns = ['产品编码', '出现次数']
# 添加排序和百分比列
product_code_counts['占比(%)'] = product_code_counts['出现次数'] / len(sale) * 100
low_frequency_products = product_code_counts[product_code_counts['出现次数'] < 50]
low_frequency_products = low_frequency_products.sort_values('出现次数', ascending=True)
low_frequency_products = low_frequency_products.reset_index(drop=True)

print("次数小于50的产品:")
print(low_frequency_products[['产品编码', '出现次数', '占比(%)']])

low_freq_codes = low_frequency_products['产品编码'].tolist()
cleaned_sale = sale[~sale['单品编码'].isin(low_freq_codes)]

directory = os.path.dirname(file_path)
filename, ext = os.path.splitext(os.path.basename(file_path))
new_file_path = os.path.join(directory, f"{filename}_已删除低频产品{ext}")

# 保存
cleaned_sale.to_excel(new_file_path, sheet_name='合并', index=False)
print(f"\n处理后的数据已保存至: {new_file_path}")


